﻿using System;
using System.Collections.Generic;
using System.Text;
using NLBS.Entity;
using System.Data;
using System.Data.Common;

namespace NLBS.Data
{
    public partial class NHibernateDataProvider : DbProvider
    {
        public override int SaveTag(Article art)
        {
            DbCommand dbCommand = db.GetSqlStringCommand("DELETE FROM blog_Tag where log_id=@logID");
            db.AddInParameter(dbCommand, "logID", DbType.Int32, art.ArticleID);
            db.ExecuteNonQuery(dbCommand);
            foreach (string tag in art.Tags)
            {
                dbCommand.Parameters.Clear();
                dbCommand = db.GetSqlStringCommand("INSERT INTO blog_Tag (log_ID, tag_Name) VALUES (@logID, @tagName)");
                db.AddInParameter(dbCommand, "logID", DbType.Int32, art.ArticleID);
                db.AddInParameter(dbCommand, "tagName", DbType.String, tag);
                db.ExecuteNonQuery(dbCommand);
            }
            return 1;

        }

        public override int DeleteTag(Article art)
        {
            DbCommand dbCommand = db.GetSqlStringCommand("DELETE FROM blog_Tag where log_id=@logID");
            db.AddInParameter(dbCommand, "logID", DbType.Int32, art.ArticleID);
            return db.ExecuteNonQuery(dbCommand);
        }

        public override IList<string> GetTagByID(Article art)
        {
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT tag_Name FROM blog_tag where log_ID=@logID");
            db.AddInParameter(dbCommand, "logID", DbType.Int32, art.ArticleID);
            IList<string> lists = new List<string>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    string tag = Convert.ToString(dataReader["tag_Name"]);
                    lists.Add(tag);
                }
            }
            return lists;
        }

        public override ITagCollection<string, Tag> GetTags()
        {
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT log_id,tag_Name FROM blog_tag");
            ITagCollection<string, Tag> lists = new TagCollection<string, Tag>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Tag tag = new Tag();
                    string tagName = Convert.ToString(dataReader["tag_Name"]);
                    int artID = Convert.ToInt32(dataReader["log_id"]);
                    tag.Name = tagName;
                    tag.Count = 1;
                    tag.ArtList.Add(artID.ToString());
                    if (lists.ContainsKey(tagName))
                    {
                        lists[tagName].Count++;
                        lists[tagName].ArtList.Add(artID.ToString());
                    }
                    else
                    {
                        lists.Add(tagName, tag);
                    }
                }
            }
            lists.MaxItem = 0;
            foreach (Tag item in lists.Values)
            {
                if (item.Count > lists.MaxItem)
                    lists.MaxItem = item.Count;
            }
            return lists;
        }

        public override IList<Article> GetArticleByIDArray(string[] idArray)
        {

            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"select cat.*,cat.cat_name as cat_name,blog_Article.*
            FROM blog_Article inner join blog_category cat on 
            blog_Article.log_catid=cat.cat_id where blog_Article.log_ID in ({0})", string.Join(",", idArray)));

            IList<Article> lists = new List<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateTopAndCommentArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);

                }
                return lists;
            }
        }

        public override IPagedCollection<Article> GetArticleByTag(int startIndex, int pageSize, string tag)
        {
            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            join blog_tag tag on art.log_id=tag.log_id and tag.tag_Name=@tag 
            left join blog_category cat on art.log_catid=cat.cat_id where art.log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article order by log_id desc) as T) order by art.log_id desc;
            SELECT COUNT(log_id) AS TotalRecords from blog_tag WHERE tag_Name=@tag", pageSize, startIndex);

            DbCommand dbCommand = db.GetSqlStringCommand(strPageSQL);
            db.AddInParameter(dbCommand, "tag", DbType.String, tag);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateTopAndCommentArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);

                }
                return lists;
            }
        }

        public override IList<string> GetArtIDByTag(string tag)
        {
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT log_id FROM blog_tag where tag_Name=@tagName");
            db.AddInParameter(dbCommand, "tagName", DbType.String, tag);
            IList<string> lists = new List<string>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    string logID = Convert.ToString(dataReader["log_id"]);
                    lists.Add(logID);
                }
            }
            return lists;
        }
    }
}
